package com.sjksy.demo.mapper;

import com.sjksy.demo.domain.Ledger;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sjksy.demo.dto.ledger.LedgerGuideStatisticDto;
import com.sjksy.demo.dto.ledger.LedgerBranchStatisticDto;
import com.sjksy.demo.dto.ledger.LedgerRouteStatisticDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
* @author guyueyuan
* @description 针对表【ledger】的数据库操作Mapper
* @createDate 2024-12-11 15:37:03
* @Entity com.sjksy.demo.domain.Ledger
*/
@Repository
@Mapper
public interface LedgerMapper extends BaseMapper<Ledger> {

    /**
     * 统计了当月每条旅游线路的收入情况
     */
    @Select("SELECT \n" +
            "    tr.route_id,\n" +
            "    tr.route_name,\n" +
            "    tr.price as route_cost,                              -- 添加线路成本\n" +
            "    COUNT(l.ledger_id) as order_count,\n" +
            "    SUM(tc.insurance) as total_insurance,                -- 添加保险费用总额\n" +
            "    SUM(tc.money) as total_income,                       -- 营业收入\n" +
            "    SUM(tc.money - tr.price - tc.insurance) as profit    -- 计算利润（收入-成本-保险）\n" +
            "FROM ledger l\n" +
            "JOIN travel_route tr ON l.route_id = tr.route_id\n" +
            "JOIN travel_contract tc ON l.guide_id = tc.guide_id AND l.route_id = tc.route_id\n" +
            "WHERE MONTH(l.time) = MONTH(CURRENT_DATE())\n" +
            "    AND YEAR(l.time) = YEAR(CURRENT_DATE())\n" +
            "GROUP BY \n" +
            "    tr.route_id, \n" +
            "    tr.route_name,\n" +
            "    tr.price\n" +
            "ORDER BY profit DESC;")
    List<LedgerRouteStatisticDto> statisticRouteLedger();

    /**
     * 统计了当月每个导游的业绩情况
     */
    @Select("SELECT \n" +
            "    g.guide_id,\n" +
            "    e.name as guide_name,\n" +
            "    g.guide_number,\n" +
            "    g.guide_level,\n" +
            "    b.branch_name as branch_name,    -- 添加分公司名称\n" +
            "    b.branch_position,               -- 添加分公司位置\n" +
            "    COUNT(l.ledger_id) as tour_count,\n" +
            "    SUM(tc.money) as total_sales\n" +
            "FROM ledger l\n" +
            "JOIN guide g ON l.guide_id = g.guide_id\n" +
            "JOIN employee e ON g.employee_id = e.employee_id\n" +
            "JOIN branch b ON e.branch_id = b.branch_id    -- 通过employee表关联branch表\n" +
            "JOIN travel_contract tc ON l.guide_id = tc.guide_id AND l.route_id = tc.route_id\n" +
            "WHERE MONTH(l.time) = MONTH(CURRENT_DATE())\n" +
            "    AND YEAR(l.time) = YEAR(CURRENT_DATE())\n" +
            "GROUP BY \n" +
            "    g.guide_id, \n" +
            "    e.name, \n" +
            "    g.guide_number, \n" +
            "    g.guide_level,\n" +
            "    b.branch_name,\n" +
            "    b.branch_position\n" +
            "ORDER BY total_sales DESC;")
    List<LedgerGuideStatisticDto> statisticGuideLedger();


    /**
     * 统计上个月各个分公司销售金额
     */
    @Select("SELECT \n" +
            "    b.branch_id,\n" +
            "    b.branch_name,\n" +
            "    b.branch_position,\n" +
            "    COUNT(l.ledger_id) as order_count,\n" +
            "    SUM(tc.money) as total_sales\n" +
            "FROM ledger l\n" +
            "JOIN branch b ON l.branch_id = b.branch_id\n" +
            "JOIN travel_contract tc ON l.guide_id = tc.guide_id AND l.route_id = tc.route_id\n" +
            "WHERE MONTH(l.time) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))\n" +
            "    AND YEAR(l.time) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))\n" +
            "GROUP BY b.branch_id, b.branch_name, b.branch_position\n" +
            "ORDER BY total_sales DESC;")
   List<LedgerBranchStatisticDto> statisticBranchLedger();


}




